package ac.kr.ml.dao;


import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ac.kr.ml.vo.ContentVo;


public class ListDao {

	public void insert(ContentVo vo){


		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

//			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
//			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");
			
			stmt = conn.createStatement();

			String sql = "insert into CONTENT (content, time, good, bad, reply_cont) values ('"
					+vo.getContent()+"', '"+vo.getTime()+"', '"
					+vo.getGood()+"', '"+vo.getBad()+"', '"
					+vo.getReply_cont()+"')";

			//ResultSet rs = stmt.executeQuery(sql);

			stmt.executeUpdate(sql);

			//rs.close();


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
	}


	
	public List<ContentVo> getAddressList()
	{
		List<ContentVo> list = new ArrayList<ContentVo>();
		
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from addressinfo order by empID asc";

			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){
				ContentVo vo = new ContentVo();
				vo.setempID(rs.getInt(1));
				vo.setdeptID(rs.getInt(2));
				vo.setdeptName(rs.getString(3));
				vo.setposName(rs.getString(4));
				vo.setuserName(rs.getString(5));
				vo.setemail(rs.getString(6));
				vo.sethp(rs.getString(7));
				list.add(vo);
			}

			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		return list;
	}
		
	
	public List<ContentVo> getList(){

		List<ContentVo> list = new ArrayList<ContentVo>();

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from CONTENT order by con_id desc";

			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){
				ContentVo vo = new ContentVo();
				vo.setCon_id(rs.getInt(1));
				vo.setContent(rs.getString(2));
				vo.setTime(rs.getString(3));
				vo.setGood(rs.getInt(4));
				vo.setBad(rs.getInt(5));
				vo.setReply_cont(rs.getInt(6));
				list.add(vo);
			}

			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		return list;
	}
	
	public ContentVo getList(int con_id){

		ContentVo vo = new ContentVo();

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");


			stmt = conn.createStatement();

			String sql = "select * from CONTENT where con_id="+con_id;

			ResultSet rs = stmt.executeQuery(sql);

			while(rs.next()){
				
				vo.setCon_id(rs.getInt(1));
				vo.setContent(rs.getString(2));
				vo.setTime(rs.getString(3));
				vo.setGood(rs.getInt(4));
				vo.setBad(rs.getInt(5));
				vo.setReply_cont(rs.getInt(6));
			}

			rs.close();

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();
			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
		return vo;
	}

	public void upGood(int good,int con_id){


		int up = 0;
		up = good +1;

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "update CONTENT set good="+up+" where con_id="+con_id;

			//ResultSet rs = stmt.executeQuery(sql);

			stmt.executeUpdate(sql);

			//rs.close();


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
	}

	public void upbad(int bad,int con_id){


		int up = 0;
		up = bad +1;

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");

			stmt = conn.createStatement();

			String sql = "update CONTENT set bad="+up+" where con_id="+con_id;

			//ResultSet rs = stmt.executeQuery(sql);

			stmt.executeUpdate(sql);

			//rs.close();


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
	}

	public void upReplyCont(int reply_cont,int con_id){


		int up = 0;
		up = reply_cont +1;

		Connection conn = null;
		Statement stmt= null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			String dburl = "jdbc:mysql://partner.medialog.co.kr:3306/freshman";
			conn = DriverManager.getConnection(dburl, "freshman", "freshman!$");
			
			stmt = conn.createStatement();

			String sql = "update CONTENT set reply_cont="+up+" where con_id="+con_id;

			//ResultSet rs = stmt.executeQuery(sql);

			stmt.executeUpdate(sql);

			//rs.close();


		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException ex){
			ex.printStackTrace();
		} finally{
			try{
				stmt.close();

			}catch(SQLException e){}
			if(conn != null){
				try{
					conn.close();
				}catch(SQLException e){}
			}

		}
	}
	
	

}
